package br.com.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;


import br.com.bean.SituacaoBean;

public class SituacaoDao {

	public void inserir(Connection conn, SituacaoBean value) throws SQLException {
		String sql = "INSERT INTO situacao(id_matricula, situacao, " +
				"data_situacao) VALUES (?, ?, ?)";

		PreparedStatement ps = conn.prepareStatement(sql);

		ps.setInt(1, value.getNmatric().getId());
		ps.setString(2, value.getSitu());
		ps.setDate(3, new java.sql.Date(value.getDatasitu().getTime()));
		ps.execute();
		ps.close();

	}

	public void excluir(Connection conn, SituacaoBean value) throws Exception {

		String sql = "DELETE FROM situacao WHERE id = ?";
		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ps.execute();
	}

	public void alterar(Connection conn, SituacaoBean value) throws Exception {

		String sql = "UPDATE situacao SET id_matricula=?, situacao=?," +
				"data_situacao=? WHERE  id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getNmatric().getId());
		ps.setString(2, value.getSitu());
		ps.setDate(3, new java.sql.Date(value.getDatasitu().getTime()));
		ps.execute();
		ps.close();
	}

	public ArrayList<SituacaoBean> getLista(Connection conn, SituacaoBean value) throws Exception{

		String sql = " SELECT id_matricula, situacao, data_situacao," +
				" FROM situacao where id = ?";

		PreparedStatement ps = conn.prepareStatement(sql);
		ps.setInt(1, value.getId());
		ResultSet rs = ps.executeQuery();
		ArrayList<SituacaoBean> al = new ArrayList<SituacaoBean>();
		while(rs.next()){
			SituacaoBean a = new SituacaoBean();
			a.getNmatric().setId(rs.getInt("id_matricula"));
			a.setSitu(rs.getString("situacao"));
			a.setDatasitu(rs.getDate("data_situacao"));
			al.add(a);
		}
		return al;
	}

	public ArrayList<SituacaoBean> getLista(Connection conn) throws Exception{


		String sql = " SELECT id_matricula, situacao, data_situacao," +
				" FROM situacao";

		PreparedStatement ps = conn.prepareStatement(sql);
		ResultSet rs = ps.executeQuery();
		ArrayList<SituacaoBean> al = new ArrayList<SituacaoBean>();
		while(rs.next()){
			SituacaoBean a = new SituacaoBean();
			a.getNmatric().setId(rs.getInt("id_matricula"));
			a.setSitu(rs.getString("situacao"));
			a.setDatasitu(rs.getDate("data_situacao"));
			al.add(a);
		}
		return al;
	}
}